<?php
namespace WDB\SQLDriver;
use WDB;
use WDB\Analyzer;
use WDB\Query\Element;
use WDB\Exception;
/**
 * Oracle SQL driver implementation
 *
 * @author Richard Ejem <richard(at)ejem.cz>
 * @package WDB
 *
 * @property-read Analyzer\View[] $views
 * @property-read Analyzer\TableView[] $tablesViews
 */
final class Oracle extends BaseSQL implements iSQLDriver
{
    /** @var resource oracle connection*/
    private $c;

    private $lastQueryString = '';

    private $lastStmt = null;

    private $charset;

    public function changeSchema($schema)
    {
        throw new Exception\UnsupportedByDriver("Oracle database does not support table schemas.");
    }

    public function schemaExists($schema)
    {
        if ($schema === NULL)
        {
            return true;
        }
        throw new Exception\UnsupportedByDriver("Oracle database does not support table schemas.");
    }

    private function getConnectionString(\WDB\Structure\ConnectionConfig $connection)
    {
        $str = $connection->host;
        if ($connection->port !== NULL)
        {
            $str .= ':'.$connection->port;
        }
        if ($connection->ociService === NULL)
        {
            $str .= '/xe';
        }
        else
        {
            $str .= '/'.$connection->ociService;
        }
        if ($connection->ociServerType === NULL)
        {
            $str .= ':'.$connection->ociServerType;
        }
        if ($connection->ociInstanceName === NULL)
        {
            $str .= '/'.$connection->ociInstanceName;
        }
    }

    public function connect(WDB\Structure\ConnectionConfig $connection)
    {
        $port = ($connection->port === NULL) ? $connection->port : ini_get("mysqli.default_port");
        $this->c = \oci_connect($connection->user, $connection->password, $this->getConnectionString($connection), $connection->charset);
        $this->charset = $connection->charset;
        $this->driver_query("alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'");
        $this->driver_query("alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss'");
    }

    public function disconnect()
    {
        \oci_close($this->c);
    }

    public function setCharset($charset)
    {
        $this->query("SET NAMES '".$this->escape_string($charset)."'");
        $this->charset = $charset;
    }

    public function getCharset()
    {
        return $this->charset;
    }

    private function driver_query($q, &$stmt = null)
    {
        $stmt = oci_parse($this->c, $q);
        return @oci_execute($stmt);
    }

    private function perform_query($q)
    {

        $result = $this->driver_query($q, $stmt);
        $this->lastQueryString = $q;
        $this->lastStmt = $stmt;
        if ($result)
        {
            return $stmt;
        }
        else
        {
            throw $this->analyzeLastError();
        }
    }

    private function analyzeLastError()
    {
        //got from http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html
        $e = oci_error($this->lastStmt);
        switch ($e['code'])
        {
            case 112:
            case 1451:
                return new WDB\Exception\QueryNotNullColumn($e['message'], $this->lastQueryString, WDB\Utils\Strings::pregRead('~^\\(([^)]+\\))$~', $e['message']));
            case 979:
                return new WDB\Exception\QueryWrongGroupField($e['message'], $this->lastQueryString, "");
            case 933:
            case 971:
            case 925: //only examples, maybe complete this list once or create better syntax error reporting
                return new WDB\Exception\QuerySyntaxError($e['message'], $this->lastQueryString);
            case 1:
                return new WDB\Exception\QueryUniqueViolation($e['message'], $this->lastQueryString, WDB\Utils\Strings::pregRead('~\\(([^)]+)\\)~', $e['message']));
            case 2291:
                return new WDB\Exception\QueryForeignChildViolation($e['message'], $this->lastQueryString, WDB\Utils\Strings::pregRead('~\\(([^)]+)\\)~', $e['message']));
            case 2292:
                return new WDB\Exception\QueryForeignParentViolation($e['message'], $this->lastQueryString, WDB\Utils\Strings::pregRead('~\\(([^\\)]+)\\)$~', $e['message']));
            case 1476:
                return new WDB\Exception\QueryDivisionByZero($e['message'], $this->lastQueryString);
            case 12899:
                return new WDB\Exception\QueryDataTooLong($e['message'], $this->lastQueryString, WDB\Utils\Strings::pregRead('~ for column (.+)$~', $e['message']), WDB\Utils\Strings::pregRead('~([0-9]+)$~', $e['message']));
            default:
                return new WDB\Exception\QueryError($e['message'], $this->lastQueryString);
        }

    }

    protected function escape_identifier($identifier)
    {
        if (strpos($identifier, "\0") !== FALSE || strpos($identifier, '"') !== FALSE)
        {
            throw new \WDB\Exception\InvalidIdentifier("Oracle identifier cannot contain a null character or double quotes");
        }
        return $identifier;
    }

    protected function quote_identifier($identifier)
    {
        return '"'.$this->escape_identifier($identifier).'"';
    }

    protected function quote_string($string)
    {
        return "'".$this->escape_string($string)."'";
    }

    protected function escape_string($string)
    {
        return str_replace("'", "''", $string);
    }

    public function queryRaw($query, $multi = FALSE)
    {
        if ($multi)
        {
            $result = true;
            foreach ($this->splitMultiQuery($query) as $q)
            {
                if (!$this->perform_query($q))
                $result = false;
            }
        }
        else
        {
            $result = $this->perform_query($query);
        }
        return new WDB\Query\RawResult(array('query'=>new WDB\Query\Raw($query), 'result'=>(bool)$result, 'nativeResult'=>$result));
    }

    public function select(WDB\Query\Select $select)
    {
        $q=$this->tosql_select($select);
        $result = $this->perform_query($q);
        $s = clone $select;
        return new Result\Oracle_SelectResult($select, $result);
    }

    public function insert(WDB\Query\Insert $insert)
    {
        $q=$this->tosql_insert($insert);
        $result = $this->perform_query($q);
        //Oracle does not support classic auto_increment values, so no insertId is possible.
        //May be once solved with OCIBindByName binding an insert statement variable into a php variable.
        return new WDB\Query\WriteResult(array('affectedRows'=>$result ? oci_num_rows($result) : 0, 'insertId'=>NULL, 'query'=>$insert, 'success'=>(bool)$result));
    }

    public function update(WDB\Query\Update $update)
    {
        if (count($update->getColumns()) == 0)
        {
            //nothing to write
            return new WDB\Query\WriteResult(array('affectedRows'=>0, 'insertId'=>NULL, 'query'=>$update, 'success'=>true));
        }
        $q=$this->tosql_update($update);
        $result = $this->perform_query($q);
        return new WDB\Query\WriteResult(array('affectedRows'=>$result ? oci_num_rows($result) : 0, 'insertId'=>NULL, 'query'=>$update, 'success'=>(bool)$result));
    }

    public function delete(WDB\Query\Delete $delete)
    {
        $q=$this->tosql_delete($delete);
        $result = $this->perform_query($q);
        return new WDB\Query\WriteResult(array('affectedRows'=>$result ? oci_num_rows($result) : 0, 'insertId'=>NULL, 'query'=>$delete));
    }

    protected function tosql_select(\WDB\Query\Select $select)
    {
        $q = "SELECT ";
        $sql_order = $this->tosql_order($select->order);
        if ($select->limit || $select->offset)
        {
            if ($select->order)
            {
                $q .= 'ROW_NUMBER() OVER (ORDER BY '.$sql_order.') ';
            }
            else
            {
                $q .= 'ROWNUM ';
            }
            $q .= '"wdb___rownum___", ';
        }
        $q .= $this->tosql_selectFieldList($select->fields);

        if ($select->table)
        {
            $q .= "\nFROM ".$this->tosql_tableSource($select->table);
        }
        if ($select->where)
        {
            $q .= "\nWHERE ".$select->where->toSQL($this);
        }
        if ($select->group)
        {
            $q .= "\nGROUP BY ".$this->tosql_selectFieldList($select->group);
        }
        if ($select->having)
        {
            $q .= "\nHAVING ".$select->having->toSQL($this);
        }
        if ($select->order)
        {
            $q .= "\nORDER BY ".$sql_order;
        }
        //emulation of LIMIT clause for oracle using ROWNUM and sub-query
        if ($select->limit || $select->offset)
        {
            $q = "SELECT * FROM ($q) WHERE ";
            if ($select->offset && $select->limit)
            {
                $q .= '"wdb___rownum___" BETWEEN '.$select->offset." AND ".($select->limit+$select->offset-1);
            }
            elseif ($select->offset)
            {
                $q .= '"wdb___rownum___" >= '.$select->offset;
            }
            else
            {
                $q .= '"wdb___rownum___" < '.($select->limit+$select->offset);
            }
        }
        if ($select->union !== NULL)
        {
            $q .= "\n UNION";
            if ($select->unionAll)
            {
                $q .= ' ALL';
            }
            $q .= "\n".$select->union->toSQL($this);
        }
        return $q;
    }

    protected function tosql_insert(WDB\Query\Insert $insert)
    {
        //mysql dialect update-like syntax INSERT INTO table SET col1=val1,col2=val2...
        //much better for human-readability and also machine building
        switch ($insert->mode)
        {
            case WDB\Query\Insert::UNIQUE:
                return $this->tosql_insert_unique($insert);
            case WDB\Query\Insert::IGNORE:
                throw new Exception\UnsupportedByDriver("IGNORE insert mode is not yet supported");
            case WDB\Query\Insert::REPLACE:
                throw new Exception\UnsupportedByDriver("REPLACE insert mode is not yet supported");
            case WDB\Query\Insert::UPDATE:
            default:
                throw new Exception\UnsupportedByDriver("UPDATE insert mode is not yet supported");
        }
    }

    private function tosql_insert_unique(WDB\Query\Insert $insert)
    {
        $q = 'INSERT INTO '.$insert->table->toSQL($this).' (';
        $tables = $insert->database->getSchema()->getTables();
        $table = $tables[$insert->table->table];
        $first = TRUE;
        $v = '';
        foreach ($insert->columns as $column=>$value)
        {
            if ($first)
            {
                $first = FALSE;
            }
            else
            {
                $q .= ', ';
                $v .= ', ';
            }
            if ($value instanceof \WDB\iExpression)
            {
                $val = $value->toSQL($this);
            }
            else
            {
                if ($value === NULL)
                {
                    $val = 'NULL';
                }
                else
                {
                    if (!isset($table->columns[$column])) throw new \WDB\Exception\NotFound("column $column not found in {$table->name}");
                    $val = $table->columns[$column]->valueToDatatype($value)->toSQL($this);
                }
            }
            $q .= $this->tosql_identifier($column);
            $v .= $val;
        }
        $q .= ') VALUES ('.$v.')';
        return $q;
    }

    public function tosql_datetime(\DateTime $value = NULL, $date, $time)
    {
        if ($value === NULL) return $this->tosql_null();
        $datePattern = 'Y-m-d';
        $timePattern = 'H:i:s';
        $datePatternSQL = 'yyyy-mm-dd';
        $timePatternSQL = 'hh24:mi:ss';
        if ($date && $time)
        {
            $d = $value->format("$datePattern $timePattern");
            $sq = "$datePatternSQL $timePatternSQL";
        }
        elseif ($date)
        {
            $d = $value->format($datePattern);
            $sq = $datePatternSQL;
        }
        elseif ($time)
        {
            $d = $value->format($timePattern);
            $sq = $timePatternSQL;
        }
        else
        {
            throw new WDBException("at least one of date or time parameters of iSQLDriver::tosql_datetime() must be TRUE");
        }
        return "to_date('$d', '$sq')";
    }
    public function tosql_time(\DateInterval $value = NULL)
    {
        $timePattern = 'H:i:s';
        $timePatternSQL = 'hh24:mi:ss';

        $d = $value->format($timePattern);
        $sq = $timePatternSQL;

        return "to_date('$d', '$sq')";
    }

    private $foreignKeyCache = NULL;

    public function getTablesViews(WDB\Analyzer\Schema $schema)
    {
        $table_list = $this->perform_query("SELECT
                uo.OBJECT_NAME AS \"name\",
                uo.OBJECT_TYPE AS \"type\",
                utc.COMMENTS AS \"comment\"
            FROM user_objects uo
            LEFT JOIN user_tab_comments utc ON uo.OBJECT_NAME=utc.TABLE_NAME
            WHERE (uo.OBJECT_TYPE = 'TABLE' OR uo.OBJECT_TYPE = 'VIEW') AND uo.STATUS != 'INVALID'");
        $tables = array();
        while ($table = oci_fetch_assoc($table_list))
        {
            if ($table['type'] == 'TABLE')
            {
                $tables[$table['name']] = new Analyzer\Table($table['name'], $table['comment'], $schema, $this);
            }
            elseif ($table['type'] == 'VIEW')
            {
                $tables[$table['name']] = new Analyzer\View($table['name'], $table['comment'], $schema, $this);
            }

        }
        return $tables;
    }

    //
    //
    // TODO: transform for oracle
    //
    //

    public function getTableColumns(Analyzer\TableView $table)
    {
        $tn = $this->quote_string($this->escape_string($table->getName()));

        //foreign key constraints
        $fk = $this->getForeignForTable($table);
        //create foreignkey structure data
        $foreign = array();
        foreach ($fk as $name=>$key)
        {
            $cols = $refCols = array();
            foreach ($key as $row)
            {
                $cols[] = $row['column'];
                $refCols[] = $row['refColumn'];
            }
            $foreign[$name] = new WDB\Structure\ForeignKeyData(array(
                'name'=>$name,
                'schema'=>NULL,
                'table'=>$table->name,
                'columns'=>$cols,
                'refSchema'=>NULL,
                'refTable'=>$key[0]['refTable'],
                'refColumns'=>$refCols,
                'onDelete'=>$this->fkeyAction($key[0]['onDelete']),
            ));
        }

        $info = $this->perform_query("SELECT
                c.COLUMN_NAME,
                c.DATA_DEFAULT,
                c.NULLABLE,
                c.CHAR_LENGTH,
                c.DATA_PRECISION,
                c.DATA_SCALE,
                ct.COMMENTS,
                c.DATA_TYPE
            FROM user_tab_columns c
                LEFT JOIN user_col_comments ct ON c.column_name=ct.column_name AND ct.table_name=".$this->quote_string($table->getName())."
            WHERE c.table_name=".$this->quote_string($table->getName())." ORDER BY column_id");
        $columns = array();
        //general column data
        while ($r = oci_fetch_assoc($info))
        {
            $colInfo = array(
                'default'=>$r['DATA_DEFAULT'],
                'nullable'=>($r['NULLABLE'] == 'Y'),
                'charMaxLength'=>$r['CHAR_LENGTH'] == 0 ? NULL : $r['CHAR_LENGTH'],
                'precision'=>$r['DATA_PRECISION'],
                'scale'=>$r['DATA_SCALE'],
                'extra'=>null,
                'comment'=>$r['COMMENTS'],
                'signed'=>true,
                'columnType'=>$r['DATA_TYPE'],
                'isAutoColumn'=>false, //not supported for oracle yet
                'isAutoIncrement'=>false, //strongly driver-specific
            );
            $columns[$r['COLUMN_NAME']] = $this->columnForType($r['DATA_TYPE'], $r['COLUMN_NAME'], $table, new WDB\Structure\ColumnMetaInfo($colInfo));
        }
        //primary and unique key constraints
        $infokeys = $this->perform_query("
                SELECT
                    cons.CONSTRAINT_TYPE,
                    cons.CONSTRAINT_NAME,
                    cols.COLUMN_NAME
                FROM all_constraints cons
                  JOIN all_cons_columns cols
                    ON cons.constraint_name = cols.constraint_name
                    AND cons.owner = cols.owner
                WHERE
                  cols.table_name=".$this->quote_string($table->name)." AND cols.owner=USER
                      AND (cons.constraint_type='U' OR cons.constraint_type='P')
                ORDER BY cols.table_name, cols.position");
        $primary = array();
        $unique = array();
        while ($r = oci_fetch_assoc($infokeys))
        {
            if ($r['CONSTRAINT_TYPE'] == 'U')
            {
                if (!isset($unique[$r['CONSTRAINT_NAME']]))
                {
                    $unique[$r['CONSTRAINT_NAME']] = array();
                }
                $unique[$r['CONSTRAINT_NAME']][] = $r['COLUMN_NAME'];
            }
            if ($r['CONSTRAINT_TYPE'] == 'P')
            {
                $primary[] = $r['COLUMN_NAME'];
            }
        }
        return new WDB\Structure\TableColumns(array('columns'=>$columns, 'unique'=>$unique, 'primary'=>$primary, 'foreign'=>$foreign));
    }

    /**
     * foreign key structure should be read by one query for whole schema because
     * this query is noticeably slow
     *
     * @param Analyzer\TableView $table
     * @return array[][][]
     */
    private function getForeignForTable($table)
    {
        $this->generateForeignKeyCache();
        if (isset($this->foreignKeyCache[$table->name]))
        {
            return $this->foreignKeyCache[$table->name];
        }
        else
        {
            return array();
        }
    }

    private function generateForeignKeyCache()
    {
        if ($this->foreignKeyCache === NULL)
        {
            $foreign = $this->perform_query("
SELECT   uc.constraint_name AS \"name\"
,        ucc1.TABLE_NAME AS \"table\"
,        ucc1.column_name AS \"column\"
,        ucc2.TABLE_NAME AS \"refTable\"
,        ucc2.column_name AS \"refColumn\"
,        uc.delete_rule AS \"onDelete\"
FROM     user_constraints uc
JOIN     user_cons_columns ucc1 ON uc.constraint_name = ucc1.constraint_name
JOIN     user_cons_columns ucc2 ON uc.r_constraint_name = ucc2.constraint_name
                                AND ucc1.POSITION = ucc2.POSITION
WHERE    uc.constraint_type = 'R'
ORDER BY ucc1.TABLE_NAME
,        uc.constraint_name");
            //transform key data to an associative array by schema and name with grouped fkeys over multiple columns to one element
            $this->foreignKeyCache = array();
            while ($row = oci_fetch_assoc($foreign))
            {
                if (!isset($this->foreignKeyCache[$row['table']]))
                        $this->foreignKeyCache[$row['table']] = array();
                if (!isset($this->foreignKeyCache[$row['table']][$row['name']]))
                        $this->foreignKeyCache[$row['table']][$row['name']] = array();
                $this->foreignKeyCache[$row['table']][$row['name']][] = $row;
            }
        }
    }

    protected function columnForType($datatype, $column_name, WDB\Analyzer\TableView $table, WDB\Structure\ColumnMetaInfo $meta)
    {
        $datatype = strtolower($datatype);
        $datatype = preg_replace('~\(.*\)$~', '', $datatype);
        switch ($datatype)
        {
            case 'char':
            case 'nchar':
            case 'varchar':
            case 'varchar2':
            case 'nvarchar2':
            case 'varbinary':
            case 'text':
            case 'blob':
            case 'clob':
                //Oracle database considers NULL to be the same value as empty string, so we can say
                //it doesn't support NULL for character columns.
                return new WDB\Analyzer\ColumnChar($column_name, $table, $meta->copy(array('nullable'=>FALSE)));
            case 'number':
                if ($meta->scale > 0)
                {
                    return new WDB\Analyzer\ColumnDecimal($column_name, $table, $meta);
                }
                else
                {
                    return new WDB\Analyzer\ColumnInteger($column_name, $table, $meta, NULL);
                }
            case 'float':
                return new WDB\Analyzer\ColumnFloat($column_name, $table, $meta, $datatype == 'double');
            case 'date':
                if (strtolower(trim($meta->default)) == 'sysdate') $meta->default = 'now';
                return new WDB\Analyzer\ColumnDateTime($column_name, $table, $meta);
            case 'time':
                if (strtolower(trim($meta->default)) == 'sysdate') $meta->default = 'now';
                return new WDB\Analyzer\ColumnTime($column_name, $table, $meta);
            case 'timestamp':
                if (strtolower(trim($meta->default)) == 'sysdate') $meta->default = 'now';
                return new WDB\Analyzer\ColumnTimestamp($column_name, $table, $meta, preg_match('~\\bon update current_timestamp\\b~i', $meta->extra));
            default:
                return new WDB\Analyzer\Column($column_name, $table, $meta);
        }
    }

    private function splitMultiQuery($q)
    {
        $result = array();
        $offset = 0;
        //remove comments
        //remove single-line comments
        $q = preg_replace('~((?:[^;\"\']|\\"(?:[^"\\\\]|\\.)\\"|\\\'(?:[^\'\\\\]|\\.)\\\')*?)--.*(?:\n|$)~', '\\1', $q);

        //remove multi-line comments
        $q_orig = $q;
        $q = '';
        while(preg_match('~((?:[^;\"\']|\\"(?:[^"\\\\]|\\.)\\"|\\\'(?:[^\'\\\\]|\\.)\\\')*?)/\\*.*?\\*/(.*)$~sm', $q_orig, $m))
        {
            $q .= $m[1].' ';
            $q_orig = $m[2];
        }
        $q .= $q_orig;



        //preg_match('~~', 'a "b" c', $m);
        //var_dump($m);
        //die();

        while (strlen($q) > 0 && preg_match('~^(?<cmd>
                (?: [^;\"\'/b]                        #any character outside string value except of ; / and b
                    |
                    (?<!\\s)b                         #b not being the keyword begin case 1
                    |
                    b(?!egin\\s*)                     #b not being the keyword begin case 2
                    |
                    (["\']).*?(?<!\\\\)(\\\\\\\\)*\2  #string value
                    |
                    (?<!\n)\\s*/\\s*(?!\r?\n)         #slash not on separate line
                    |
                    begin (?: [^/]*                   #any character except of /
                        |
                        (?<!\n)\\s*/\\s*(?!\r?\n)     #slash not on separate line
                    )

                )*
            )(?<tail>.*)$~xsmi', $q, $matches, NULL))
        {
            if (!preg_match('~^\\s*$~', $matches['cmd']))
            {
                $result[] = $matches['cmd'];
            }
            $q = preg_replace('~^[\\s;/]*~', '', $matches['tail']);
        }
        return $result;
    }

    public function startTransaction()
    {
        $this->queryRaw("START TRANSACTION");
    }
}